package main

import (
	"database/sql"
	"fmt"
	"goBasic/ketanx/help"
	_"github.com/go-sql-driver/mysql"
)

var databaseUrl = "root:123@tcp(127.0.0.1:3306)/testdb?charset=utf8"

func main()  {
	//test1()
	//test2()
	//test3()
	//test4()

	//test5()
	//test6()

	//test7()
	//test8()

	test9()
}

/**
连接数据库
 */
func test1()  {
	db, err := sql.Open("mysql", databaseUrl)
	help.CheckError(err)

	fmt.Println(db)

	rows,_ := db.Query("select * from testdb.person")
	fmt.Println(rows)
}


/**
插入数据  方式1
 */
func test2()  {
	//1.连接数据库
	db, err := sql.Open("mysql", databaseUrl)
	help.CheckError(err)

	//2.执行插入数据
	result, err := db.Exec("insert into person(id, name) values (?, ?)", 3, "wangwu");
	help.CheckError(err)

	id, err := result.LastInsertId()
	help.CheckError(err)

	count,err :=result.RowsAffected()
	help.CheckError(err)

	fmt.Println("插入数据id:", id, ", 影响行数:", count)
}

/*
插入数据	方式2
 */
func test3()  {
	//连接数据
	db,err := sql.Open("mysql", databaseUrl)
	help.CheckError(err)

	//获取 stmt
	stmt,err := db.Prepare("insert into person(`id`, `name`) values(?, ?)")
	help.CheckError(err)

	//执行sql
	result,err := stmt.Exec(4, "zhaoliu")
	help.CheckError(err)

	id, _ := result.LastInsertId()
	count, _ := result.RowsAffected()

	fmt.Println("插入数据id:", id, ", 影响行数:", count)
}

/*
插入多条数据
 */
func test4()  {
	//连接数据库
	db,err := sql.Open("mysql", databaseUrl)
	help.CheckError(err)

	//拼接sql
	sqlStr := "insert into person(`id`, `name`) values(?, ?), (?,?), (?,?)"

	//获取stmt
	stmt, err := db.Prepare(sqlStr)
	help.CheckError(err)

	//执行sql
	result,err := stmt.Exec(11, "zhangsan11", 12, "zhangsan12", 13, "zhangsan13")
	help.CheckError(err)

	//
	id ,_ := result.LastInsertId()
	count,_:=result.RowsAffected()

	fmt.Println("插入数据id:", id, ", 影响行数:", count)

}

/*
更新数据
*/
func test5()  {
	//连接数据库
	db,err := sql.Open("mysql", databaseUrl)
	help.CheckError(err)

	//获取stmt
	stmt,err := db.Prepare("update person set name = ?  where id = ?")
	help.CheckError(err)

	//执行sql
	result,err := stmt.Exec("zhangsan00", 1)
	help.CheckError(err)

	id,_ 	:= result.LastInsertId()
	count,_:= result.RowsAffected()

	fmt.Println("插入数据id:", id, ", 影响行数:", count)

}


/**
删除数据
 */
func test6()  {
	//连接数据库
	db,err := sql.Open("mysql", databaseUrl)
	help.CheckError(err)

	//获取stmt
	stmt,err := db.Prepare("delete from person where id =?")
	help.CheckError(err)

	//执行sql
	result,err := stmt.Exec(13)
	help.CheckError(err)

	id,err := result.LastInsertId()
	count,err := result.RowsAffected()

	fmt.Println("插入数据id:", id, ", 影响行数:", count)

}

/*
查询  单条数据
 */
func test7()  {
	//链接数据库
	db,err := sql.Open("mysql", databaseUrl)
	help.CheckError(err)

	//执行sql
	id ,name := 0, ""
	err = db.QueryRow("select * from person where id = ?", 3).Scan(&id, &name)
	help.CheckError(err)		//返回结果 的列数 和 给参数列数 要相同

	//查询结果
	fmt.Println("id:", id, ", name:", name)

}




/**
查询  多条记录
 */
func test8()  {
	//连接数据库
	db,err := sql.Open("mysq1l", databaseUrl)
	help.CheckError(err)

	//执行sql
	rows,err := db.Query("select * from person")
	help.CheckError(err)

	//获取查询结果
	id, name := 0, ""

	for rows.Next() {
		err = rows.Scan(&id, &name)
		help.CheckError(err)
		fmt.Println("--", id, name)
	}

	rows.Close()
	db.Close()
}


//测试  工具类获取连接， 工具类关闭连接
func test9()  {
	//获取db连接
	db,err := help.GetDB()
	help.CheckError(err)

	//关闭连接
	defer help.CloseDB(db)

	//执行sql
	rows,err := db.Query("select * from person where id < ?", 10)
	help.CheckError(err)

	//获取查询结果
	for rows.Next() {
		id, name := 0, ""
		rows.Scan(&id, &name)

		fmt.Println(id, name)
	}

}










